<?php
	function f230($sql){
		
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("230");


		$dataType = getValue($_GET['dataType']);
		$action = getValue($_GET['action']);
		
		if($dataType == "json"){
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				$query = getQuery($startTime, $endTime);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else if($action == "getLines"){
			$query = getLines();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "230: Bad request";
			print(json_encode($temp));
		}

	}
	function getQuery(&$startTime, &$endTime){
		$line 			= getValue($_GET["line"]);
		$shift 			= getValue($_GET["shift"]);
		$shiftPerDay 	= getValue($_GET["shiftPerDay"]);

		if($line == "" || $line == "all")
			$line = 0;

		if($shift == "" || $shift == "all")
			$shift = 0;

		if($shiftPerDay == "")
			$shiftPerDay = 1;

		$queryString = 
			"
			SELECT 
				line_id,
				'value' as dataType,
				speed,
				pouches,
				pouches_YTD,
				hours,
				hours_YTD,
				PE,
				PE_YTD,
				TPDT,
				TPDT_YTD,
				TE,
				TE_YTD,
				usage,
				usage_YTD,
				change_over,
				change_over_YTD
			FROM lin_production_stat_sum(
									CONVERT(datetime,'".$startTime."',104),
									CONVERT(datetime,'".$endTime."',104),
									".$line.",--dataFilter_ProdStat.Line
									".$shiftPerDay.",--txtStatistics_ShiftsPerDaya
									".$shift."--dataFilter_ProdStat.Shift
									)
			union

			SELECT 
				line_id,
				'total' as dataType,
				null as speed,
				sum(	pouches				)	as	pouches,
				sum(	pouches_YTD			)	as	pouches_YTD,
				sum(	hours				)	as	hours,
				sum(	hours_YTD			)	as	hours_YTD,
				null as PE,
				null as PE_YTD,
				sum(	TPDT				)	as	TPDT,
				sum(	TPDT_YTD			)	as	TPDT_YTD,
				null as TE,
				null as TE_YTD,
				sum(	usage				)	as	usage,
				sum(	usage_YTD			)	as	usage_YTD,
				sum(	change_over			)	as	change_over,
				sum(	change_over_YTD		)	as	change_over_YTD
			FROM lin_production_stat_sum(
									CONVERT(datetime,'".$startTime."',104),
									CONVERT(datetime,'".$endTime."',104),
									".$line.",--dataFilter_ProdStat.Line
									".$shiftPerDay.",--txtStatistics_ShiftsPerDaya
									".$shift."--dataFilter_ProdStat.Shift
									)
			GROUP BY line_id
			ORDER BY line_id ASC, speed ASC;

		";
		return $queryString;
	}
	function getYears(){
		$queryString = 
			"SELECT DISTINCT 
				DATEPART(year, modified_Date) AS availableYear 
			FROM lin_Produced 
			ORDER BY availableYear desc";
		return $queryString;
	}
	function getLines(){
		$queryString = 
			"SELECT DISTINCT 
				line_id AS availableLine 
			FROM lin_Produced 
			ORDER BY availableLine asc";
		return $queryString;
	}
?>